package de.poketradeshop.database;

import java.io.Serializable;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;
import java.util.logging.Logger;

import javax.enterprise.context.SessionScoped;
import javax.inject.Named;

import com.mysql.jdbc.PreparedStatement;

import de.poketradeshop.ui.model.Customer;
import de.poketradeshop.ui.model.Order;
import de.poketradeshop.ui.model.Pokemon;
import de.poketradeshop.ui.model.PokemonProperty;
import de.poketradeshop.ui.model.Product;

@Named
@SessionScoped
public class DatabaseService implements Serializable {

	private Connection connection;
	private final Logger logger = Logger.getLogger(this.getClass().getName());

	public boolean isConnected() {
		return connection != null;
	}

	public void connect() {
		try {

			Properties prop = new Properties();

			prop.load(DatabaseService.class.getClassLoader()
					.getResourceAsStream("db.properties"));

			// FileInputStream in = new FileInputStream(
			// System.getProperty("WEB-INF/db.properties"));
			// prop.load(in);
			// in.close();

			String driver = prop.getProperty("jdbc.driver");

			if (driver != null) {
				Class.forName(driver);
			}

			String connectionURL = prop.getProperty("jdbc.url");
			String username = prop.getProperty("jdbc.username");
			String password = prop.getProperty("jdbc.password");

			Class.forName(driver);
			connection = DriverManager.getConnection(connectionURL, username,
					password);
			logger.info("Connection successful!");

			//OLD CONNECTION 
			// Class.forName("com.mysql.jdbc.Driver");
			// connection =
			// DriverManager.getConnection("jdbc:mysql://localhost:3306/poketradeshop",
			// "root", "admin");

		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	public boolean passwordMatches(String email, String customerPassword) {
		String password = "";
		try {

			if (!isConnected())
				connect();

			PreparedStatement prepStmt = (PreparedStatement) connection.prepareStatement("SELECT password FROM customer WHERE email = ?");
			prepStmt.setString(1, email);
			ResultSet resultSet = prepStmt.executeQuery();
			
			while (resultSet.next()) {
				password = resultSet.getString("password");
			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return (password.equals(customerPassword));
	}

	public boolean emailExists(String email) {

		try {

			if (!isConnected())
				connect();

			PreparedStatement prepStmt = (PreparedStatement) connection.prepareStatement("SELECT email FROM customer WHERE email = ?");
			prepStmt.setString(1, email);
			ResultSet resultSet = prepStmt.executeQuery();
			
			while (resultSet.next()) {
				return true;
			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return false;
	}

	public boolean passwordCheck(String email, String passwordToCheck) {
		String password = "";
		try {

			if (!isConnected())
				connect();

			PreparedStatement prepStmt = (PreparedStatement) connection.prepareStatement("SELECT password FROM customer WHERE email = ?");
			prepStmt.setString(1, email);
			ResultSet resultSet = prepStmt.executeQuery();

			while (resultSet.next()) {
				password = resultSet.getString(1);
			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return (password.equals(passwordToCheck));
	}

	public String getNameByEmail(String email) {
		String name = "";
		try {

			if (!isConnected())
				connect();

			PreparedStatement prepStmt = (PreparedStatement) connection.prepareStatement("SELECT name FROM customer WHERE email = ?");
			prepStmt.setString(1, email);
			ResultSet resultSet = prepStmt.executeQuery();
			
			while (resultSet.next()) {
				name = resultSet.getString(1);
			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return name;
	}

	public int getCustomerIdByEmail(String email) {
		int customerId = 0;
		try {

			if (!isConnected())
				connect();

			PreparedStatement prepStmt = (PreparedStatement) connection.prepareStatement("SELECT id FROM customer WHERE email = ?");
			prepStmt.setString(1, email);
			ResultSet resultSet = prepStmt.executeQuery();
			
			while (resultSet.next()) {
				customerId = resultSet.getInt("id");
			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return customerId;
	}

	public int createCustomer(String email, String password, String city,
			String friendcode, String name, String postcode, String street,
			String telephone, String bankDepositor, String bankCode,
			String bankAccountNo) {
		int customerid = 0;
		try {

			if (!isConnected())
				connect();

			// Create Customer in Database
			PreparedStatement prepStmt = 
					(PreparedStatement) connection.prepareStatement
						("INSERT INTO customer (email, password, city, friendcode, name, postcode, street, telephone, bank_depositor, bank_code, bank_account_no) "
								+ "VALUES (?,?,?,?,?,?,?,?,?,?,?)");
			prepStmt.setString(1, email);
			prepStmt.setString(2, password);
			prepStmt.setString(3, city);
			prepStmt.setString(4, friendcode);
			prepStmt.setString(5, name);
			prepStmt.setString(6, postcode);
			prepStmt.setString(7, street);
			prepStmt.setString(8, telephone);
			prepStmt.setString(9, bankDepositor);
			prepStmt.setString(10, bankCode);
			prepStmt.setString(11, bankAccountNo);
			prepStmt.executeUpdate();
			
			logger.info("Query " + prepStmt + " executed!");

		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return customerid;
	}

	public void updateCustomer(int id, String email, String password,
			String city, String friendcode, String name, String postcode,
			String street, String telephone, String bankDepositor,
			String bankCode, String bankAccountNo) {

		try {

			if (!isConnected())
				connect();
			
			// Update Customer in Database
			PreparedStatement prepStmt = (PreparedStatement) connection.prepareStatement
					("UPDATE customer SET password = ?, city = ?, friendcode = ?, name = ?, "
							+ "postcode = ?, street = ?, telephone = ?, bank_depositor = ?, bank_code = ?, bank_account_no = ? WHERE id = ?");
			prepStmt.setString(1, password);
			prepStmt.setString(2, city);
			prepStmt.setString(3, friendcode);
			prepStmt.setString(4, name);
			prepStmt.setString(5, postcode);
			prepStmt.setString(6, street);
			prepStmt.setString(7, telephone);
			prepStmt.setString(8, bankDepositor);
			prepStmt.setString(9, bankCode);
			prepStmt.setString(10, bankAccountNo);
			prepStmt.setInt(11, id);
			prepStmt.executeUpdate();

			logger.info("Query " + prepStmt + " executed!");

		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	public void updateCustomer(int id, String email, String city,
			String friendcode, String name, String postcode, String street,
			String telephone, String bankDepositor, String bankCode,
			String bankAccountNo) {

		try {

			if (!isConnected())
				connect();
			
			// Update Customer in Database
			PreparedStatement prepStmt = (PreparedStatement) connection.prepareStatement
					("UPDATE customer SET city = ?, friendcode = ?, name = ?, "
							+ "postcode = ?, street = ?, telephone = ?, bank_depositor = ?, bank_code = ?, bank_account_no = ? WHERE id = ?");
			prepStmt.setString(1, city);
			prepStmt.setString(2, friendcode);
			prepStmt.setString(3, name);
			prepStmt.setString(4, postcode);
			prepStmt.setString(5, street);
			prepStmt.setString(6, telephone);
			prepStmt.setString(7, bankDepositor);
			prepStmt.setString(8, bankCode);
			prepStmt.setString(9, bankAccountNo);
			prepStmt.setInt(10, id);
			prepStmt.executeUpdate();

			logger.info("Query " + prepStmt + " executed!");

		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	public Customer getCustomerByID(int customerId) {
		Customer customer = new Customer();
		try {

			if (!isConnected())
				connect();

			Statement statement = connection.createStatement();
			String query = "SELECT email, password, name, city, postcode, street, telephone, friendcode, bank_account_no, bank_code, bank_depositor, company FROM customer WHERE id = "
					+ customerId;
			ResultSet resultSet = statement.executeQuery(query);
			logger.info("Query " + query + " executed!");
			while (resultSet.next()) {
				customer.setEmail(resultSet.getString("email"));
				customer.setPassword(resultSet.getString("password"));
				customer.setName(resultSet.getString("name"));
				customer.setCity(resultSet.getString("city"));
				customer.setPostcode(resultSet.getString("postcode"));
				customer.setStreet(resultSet.getString("street"));
				customer.setTelephone(resultSet.getString("telephone"));
				customer.setFriendcode(resultSet.getString("friendcode"));
				customer.setBankAccountNo(resultSet
						.getString("bank_account_no"));
				customer.setBankCode(resultSet.getString("bank_code"));
				customer.setBankDepositor(resultSet.getString("bank_depositor"));
				customer.setCompany(resultSet.getString("company"));
			}

		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return customer;
	}

	//not used in this version: neccessary for admin area
	public Pokemon getPokemonById(String id) {
		Pokemon pokemon = new Pokemon();

		try {
			if (!isConnected())
				connect();

			Statement statement = connection.createStatement();
			String query = "SELECT legendary, name, type1, type2 FROM pokemon WHERE id = "
					+ id;
			ResultSet resultSet = statement.executeQuery(query);
			logger.info("Query " + query + " executed!");
			while (resultSet.next()) {
				pokemon.setLegendary(resultSet.getBoolean("legendary"));
				pokemon.setName(resultSet.getString("name"));
				pokemon.setType1(resultSet.getString("type1"));
				pokemon.setType2(resultSet.getString("type"));
			}

		} catch (Exception e) {
			// TODO: handle exception
		}
		return pokemon;

	}

	//not used in this version: neccessary for admin area
	public PokemonProperty getPokemonAttackById(int id) {
		PokemonProperty pokemonAttack = new PokemonProperty();

		try {
			if (!isConnected())
				connect();

			Statement statement = connection.createStatement();
			String query = "SELECT attack FROM pokemon_attack WHERE id = " + id;
			ResultSet resultSet = statement.executeQuery(query);
			logger.info("Query " + query + " executed!");
			while (resultSet.next()) {
				pokemonAttack.setProperty(resultSet.getString("attack"));
			}

		} catch (Exception e) {
			// TODO: handle exception
		}
		return pokemonAttack;
	}

	//not used in this version: neccessary for admin area
	public PokemonProperty getPokemonDvById(int id) {
		PokemonProperty pokemonDv = new PokemonProperty();

		try {
			if (!isConnected())
				connect();

			Statement statement = connection.createStatement();
			String query = "SELECT sentence FROM pokemon_dv WHERE id = " + id;
			ResultSet resultSet = statement.executeQuery(query);
			logger.info("Query " + query + " executed!");
			while (resultSet.next()) {
				pokemonDv.setProperty(resultSet.getString("sentence"));
			}

		} catch (Exception e) {
			// TODO: handle exception
		}
		return pokemonDv;
	}

	//not used in this version: neccessary for admin area
	public PokemonProperty getPokemonGenderById(int id) {
		PokemonProperty pokemonGender = new PokemonProperty();

		try {
			if (!isConnected())
				connect();

			Statement statement = connection.createStatement();
			String query = "SELECT gender FROM pokemon_gender WHERE id = " + id;
			ResultSet resultSet = statement.executeQuery(query);
			logger.info("Query " + query + " executed!");
			while (resultSet.next()) {
				pokemonGender.setProperty(resultSet.getString("gender"));
			}

		} catch (Exception e) {
			// TODO: handle exception
		}
		return pokemonGender;
	}

	//not used in this version: neccessary for admin area
	public PokemonProperty getPokemonNatureById(int id) {
		PokemonProperty pokemonNature = new PokemonProperty();

		try {
			if (!isConnected())
				connect();

			Statement statement = connection.createStatement();
			String query = "SELECT nature FROM pokemon_nature WHERE id = " + id;
			ResultSet resultSet = statement.executeQuery(query);
			logger.info("Query " + query + " executed!");
			while (resultSet.next()) {
				pokemonNature.setProperty(resultSet.getString("nature"));
			}

		} catch (Exception e) {
			// TODO: handle exception
		}
		return pokemonNature;
	}

	//not used in this version: neccessary for admin area
	public PokemonProperty getPokemonAbiltiyById(int id) {
		PokemonProperty pokemonAbility = new PokemonProperty();

		try {
			if (!isConnected())
				connect();

			Statement statement = connection.createStatement();
			String query = "SELECT ability FROM pokemon_ability WHERE id = "
					+ id;
			ResultSet resultSet = statement.executeQuery(query);
			logger.info("Query " + query + " executed!");
			while (resultSet.next()) {
				pokemonAbility.setProperty(resultSet.getString("ability"));
			}

		} catch (Exception e) {
			// TODO: handle exception
		}
		return pokemonAbility;
	}

	//not used in this version: neccessary for admin area
	public List<Pokemon> getAllPokemons() {
		ArrayList<Pokemon> pokemonArray = new ArrayList<Pokemon>();
		try {

			if (!isConnected())
				connect();

			Statement statement = connection.createStatement();
			String query = "SELECT legendary, name, type1, type2 FROM pokemon";
			ResultSet resultSet = statement.executeQuery(query);
			logger.info("Query " + query + " executed!");

			while (resultSet.next()) {
				Pokemon pokemon = new Pokemon();
				pokemon.setLegendary(resultSet.getBoolean("legendary"));
				pokemon.setName(resultSet.getString("name"));
				pokemon.setType1(resultSet.getString("type1"));
				pokemon.setType2(resultSet.getString("type2"));
				pokemonArray.add(pokemon);
			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return pokemonArray;
	}

	public List<Product> getProducts() {
		ArrayList<Product> pokemonArray = new ArrayList<Product>();
		try {

			if (!isConnected())
				connect();

			Statement statement = connection.createStatement();
			String query = "SELECT a.id, a.shiny, a.ev_atk, a.ev_def, a.ev_hp, a.ev_init, a.ev_spatk, a.ev_spdef, a.level, "
					+ "a.normal_price, a.special_price, b.id, b.name, b.type1, b.type2, c.ability, d.attack, h.attack, i.attack, j.attack, "
					+ "f.gender, g.nature FROM product as a LEFT JOIN pokemon as b on  a.pokemon_id = b.id LEFT JOIN pokemon_ability "
					+ "as c on a.pokemon_ability_id = c.id LEFT JOIN pokemon_attack as d on  a.pokemon_attack_id1 = d.id LEFT JOIN "
					+ "pokemon_attack as h on a.pokemon_attack_id2 = h.id LEFT JOIN pokemon_attack as  i on a.pokemon_attack_id3 = "
					+ "i.id LEFT JOIN pokemon_attack as j on a.pokemon_attack_id4 = j.id LEFT JOIN pokemon_dv as  e on a.pokemon_dv_id = "
					+ "e.id LEFT JOIN pokemon_gender as f on a.pokemon_gender_id = f.id LEFT JOIN pokemon_nature as  g on a.pokemon_nature_id = g.id WHERE a.visible = 1";
			ResultSet resultSet = statement.executeQuery(query);
			logger.info("Query " + query + " executed!");

			while (resultSet.next()) {
				Product product = new Product();
				product.setProductId(resultSet.getInt("a.id"));
				product.setEvAtk(resultSet.getInt("a.ev_atk"));
				product.setEvDef(resultSet.getInt("a.ev_def"));
				product.setEvHp(resultSet.getInt("a.ev_hp"));
				product.setEvInit(resultSet.getInt("a.ev_init"));
				product.setEvSpatk(resultSet.getInt("a.ev_spatk"));
				product.setEvSpdef(resultSet.getInt("a.ev_spdef"));
				product.setLevel(resultSet.getInt("a.level"));
				product.setNormalPrice(resultSet.getFloat("a.normal_price"));
				product.setSpecialPrice(resultSet.getFloat("a.special_price"));
				product.setShiny(resultSet.getBoolean("a.shiny"));
				product.setId(resultSet.getInt("b.id"));
				product.setName(resultSet.getString("b.name"));
				product.setType1(resultSet.getString("b.type1"));
				product.setAbility(resultSet.getString("c.ability"));
				product.setAttack1(resultSet.getString("d.attack"));
				product.setAttack2(resultSet.getString("h.attack"));
				product.setAttack3(resultSet.getString("i.attack"));
				product.setAttack4(resultSet.getString("j.attack"));
				product.setGender(resultSet.getString("f.gender"));
				product.setNature(resultSet.getString("g.nature"));

				if (resultSet.getString("b.type2") != null) {
					product.setType2("/ " + resultSet.getString("b.type2"));
				}

				pokemonArray.add(product);
			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return pokemonArray;
	}

	public List<Product> getProductsBySearchBox(String pokemonName) {
		ArrayList<Product> pokemonArray = new ArrayList<Product>();
		try {

			if (!isConnected())
				connect();

			PreparedStatement prepStmt = (PreparedStatement) connection.prepareStatement("SELECT a.id, a.shiny, a.ev_atk, a.ev_def, a.ev_hp, a.ev_init, a.ev_spatk, a.ev_spdef, a.level, a.normal_price, a.special_price, b.id, b.name, b.type1, "
					+ "b.type2, c.ability, d.attack, h.attack, i.attack, j.attack, f.gender, g.nature FROM product as a LEFT JOIN pokemon as b on  a.pokemon_id = b.id LEFT JOIN "
					+ "pokemon_ability as c on a.pokemon_ability_id = c.id LEFT JOIN pokemon_attack as d on  a.pokemon_attack_id1 = d.id LEFT JOIN pokemon_attack as h on "
					+ "a.pokemon_attack_id2 = h.id LEFT JOIN pokemon_attack as  i on a.pokemon_attack_id3 = i.id LEFT JOIN pokemon_attack as j on a.pokemon_attack_id4 = j.id "
					+ "LEFT JOIN pokemon_dv as  e on a.pokemon_dv_id = e.id LEFT JOIN pokemon_gender as f on a.pokemon_gender_id = f.id LEFT JOIN pokemon_nature as  g on "
					+ "a.pokemon_nature_id = g.id WHERE a.visible = 1 AND b.name LIKE ?");
			prepStmt.setString(1, "%" + pokemonName + "%");
			ResultSet resultSet = prepStmt.executeQuery();
			logger.info("Query " + prepStmt + " executed!");
			
			
			while (resultSet.next()) {
				Product product = new Product();
				product.setProductId(resultSet.getInt("a.id"));
				product.setEvAtk(resultSet.getInt("a.ev_atk"));
				product.setEvDef(resultSet.getInt("a.ev_def"));
				product.setEvHp(resultSet.getInt("a.ev_hp"));
				product.setEvInit(resultSet.getInt("a.ev_init"));
				product.setEvSpatk(resultSet.getInt("a.ev_spatk"));
				product.setEvSpdef(resultSet.getInt("a.ev_spdef"));
				product.setLevel(resultSet.getInt("a.level"));
				product.setNormalPrice(resultSet.getFloat("a.normal_price"));
				product.setSpecialPrice(resultSet.getFloat("a.special_price"));
				product.setShiny(resultSet.getBoolean("a.shiny"));
				product.setId(resultSet.getInt("b.id"));
				product.setName(resultSet.getString("b.name"));
				product.setType1(resultSet.getString("b.type1"));
				product.setAbility(resultSet.getString("c.ability"));
				product.setAttack1(resultSet.getString("d.attack"));
				product.setAttack2(resultSet.getString("h.attack"));
				product.setAttack3(resultSet.getString("i.attack"));
				product.setAttack4(resultSet.getString("j.attack"));
				product.setGender(resultSet.getString("f.gender"));
				product.setNature(resultSet.getString("g.nature"));

				if (resultSet.getString("b.type2") != null) {
					product.setType2("/ " + resultSet.getString("b.type2"));
				}

				pokemonArray.add(product);
			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return pokemonArray;
	}

	public List<Product> getProductsByTypeFilter(String filter) {
		ArrayList<Product> pokemonArray = new ArrayList<Product>();
		try {

			if (!isConnected())
				connect();

			Statement statement = connection.createStatement();
			String query = "SELECT a.id, a.shiny, a.ev_atk, a.ev_def, a.ev_hp, a.ev_init, a.ev_spatk, a.ev_spdef, a.level, "
					+ "a.normal_price, a.special_price, b.id, b.name, b.type1, b.type2, c.ability, d.attack, h.attack, i.attack, j.attack, "
					+ "f.gender, g.nature FROM product as a LEFT JOIN pokemon as b on  a.pokemon_id = b.id LEFT JOIN pokemon_ability "
					+ "as c on a.pokemon_ability_id = c.id LEFT JOIN pokemon_attack as d on  a.pokemon_attack_id1 = d.id LEFT JOIN "
					+ "pokemon_attack as h on a.pokemon_attack_id2 = h.id LEFT JOIN pokemon_attack as  i on a.pokemon_attack_id3 = "
					+ "i.id LEFT JOIN pokemon_attack as j on a.pokemon_attack_id4 = j.id LEFT JOIN pokemon_dv as  e on a.pokemon_dv_id = "
					+ "e.id LEFT JOIN pokemon_gender as f on a.pokemon_gender_id = f.id LEFT JOIN pokemon_nature as  g on a.pokemon_nature_id = g.id WHERE (b.type1 = '"
					+ filter
					+ "' OR b.type2 = '"
					+ filter
					+ "') AND a.visible = 1";
			ResultSet resultSet = statement.executeQuery(query);
			logger.info("Query " + query + " executed!");

			while (resultSet.next()) {
				Product product = new Product();
				product.setProductId(resultSet.getInt("a.id"));
				product.setEvAtk(resultSet.getInt("a.ev_atk"));
				product.setEvDef(resultSet.getInt("a.ev_def"));
				product.setEvHp(resultSet.getInt("a.ev_hp"));
				product.setEvInit(resultSet.getInt("a.ev_init"));
				product.setEvSpatk(resultSet.getInt("a.ev_spatk"));
				product.setEvSpdef(resultSet.getInt("a.ev_spdef"));
				product.setLevel(resultSet.getInt("a.level"));
				product.setNormalPrice(resultSet.getFloat("a.normal_price"));
				product.setSpecialPrice(resultSet.getFloat("a.special_price"));
				product.setShiny(resultSet.getBoolean("a.shiny"));
				product.setId(resultSet.getInt("b.id"));
				product.setName(resultSet.getString("b.name"));
				product.setType1(resultSet.getString("b.type1"));
				product.setAbility(resultSet.getString("c.ability"));
				product.setAttack1(resultSet.getString("d.attack"));
				product.setAttack2(resultSet.getString("h.attack"));
				product.setAttack3(resultSet.getString("i.attack"));
				product.setAttack4(resultSet.getString("j.attack"));
				product.setGender(resultSet.getString("f.gender"));
				product.setNature(resultSet.getString("g.nature"));

				if (resultSet.getString("b.type2") != null) {
					product.setType2("/ " + resultSet.getString("b.type2"));
				}

				pokemonArray.add(product);
			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return pokemonArray;
	}

	public List<Product> getProductsByLevelFilter(String filter) {
		ArrayList<Product> pokemonArray = new ArrayList<Product>();
		try {

			if (!isConnected())
				connect();

			Statement statement = connection.createStatement();
			String query = "SELECT a.id, a.ev_atk, a.ev_def, a.ev_hp, a.ev_init, a.ev_spatk, a.ev_spdef, a.level, "
					+ "a.normal_price, a.special_price, a.shiny, b.id, b.name, b.type1, b.type2, c.ability, d.attack, h.attack, i.attack, j.attack, "
					+ "f.gender, g.nature FROM product as a LEFT JOIN pokemon as b on  a.pokemon_id = b.id LEFT JOIN pokemon_ability "
					+ "as c on a.pokemon_ability_id = c.id LEFT JOIN pokemon_attack as d on  a.pokemon_attack_id1 = d.id LEFT JOIN "
					+ "pokemon_attack as h on a.pokemon_attack_id2 = h.id LEFT JOIN pokemon_attack as  i on a.pokemon_attack_id3 = "
					+ "i.id LEFT JOIN pokemon_attack as j on a.pokemon_attack_id4 = j.id LEFT JOIN pokemon_dv as  e on a.pokemon_dv_id = "
					+ "e.id LEFT JOIN pokemon_gender as f on a.pokemon_gender_id = f.id LEFT JOIN pokemon_nature as  g on a.pokemon_nature_id = g.id WHERE a.level = '"
					+ filter + "' AND a.visible = 1";
			ResultSet resultSet = statement.executeQuery(query);
			logger.info("Query " + query + " executed!");

			while (resultSet.next()) {
				Product product = new Product();
				product.setProductId(resultSet.getInt("a.id"));
				product.setEvAtk(resultSet.getInt("a.ev_atk"));
				product.setEvDef(resultSet.getInt("a.ev_def"));
				product.setEvHp(resultSet.getInt("a.ev_hp"));
				product.setEvInit(resultSet.getInt("a.ev_init"));
				product.setEvSpatk(resultSet.getInt("a.ev_spatk"));
				product.setEvSpdef(resultSet.getInt("a.ev_spdef"));
				product.setLevel(resultSet.getInt("a.level"));
				product.setNormalPrice(resultSet.getFloat("a.normal_price"));
				product.setSpecialPrice(resultSet.getFloat("a.special_price"));
				product.setId(resultSet.getInt("b.id"));
				product.setShiny(resultSet.getBoolean("a.shiny"));
				product.setName(resultSet.getString("b.name"));
				product.setType1(resultSet.getString("b.type1"));
				product.setAbility(resultSet.getString("c.ability"));
				product.setAttack1(resultSet.getString("d.attack"));
				product.setAttack2(resultSet.getString("h.attack"));
				product.setAttack3(resultSet.getString("i.attack"));
				product.setAttack4(resultSet.getString("j.attack"));
				product.setGender(resultSet.getString("f.gender"));
				product.setNature(resultSet.getString("g.nature"));

				if (resultSet.getString("b.type2") != null) {
					product.setType2("/ " + resultSet.getString("b.type2"));
				}

				pokemonArray.add(product);
			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return pokemonArray;
	}

	public List<Product> getProductsBySpecialFilter(String filter) {
		ArrayList<Product> pokemonArray = new ArrayList<Product>();
		try {

			if (!isConnected())
				connect();

			Statement statement = connection.createStatement();
			String query = "SELECT a.id, a.ev_atk, a.ev_def, a.ev_hp, a.ev_init, a.ev_spatk, a.ev_spdef, a.level, a.shiny, a.event, "
					+ "a.normal_price, a.special_price, b.id, b.name, b.type1, b.type2, b.legendary, c.ability, d.attack, h.attack, i.attack, j.attack, "
					+ "f.gender, g.nature FROM product as a LEFT JOIN pokemon as b on  a.pokemon_id = b.id LEFT JOIN pokemon_ability "
					+ "as c on a.pokemon_ability_id = c.id LEFT JOIN pokemon_attack as d on  a.pokemon_attack_id1 = d.id LEFT JOIN "
					+ "pokemon_attack as h on a.pokemon_attack_id2 = h.id LEFT JOIN pokemon_attack as  i on a.pokemon_attack_id3 = "
					+ "i.id LEFT JOIN pokemon_attack as j on a.pokemon_attack_id4 = j.id LEFT JOIN pokemon_dv as  e on a.pokemon_dv_id = "
					+ "e.id LEFT JOIN pokemon_gender as f on a.pokemon_gender_id = f.id LEFT JOIN pokemon_nature as  g on a.pokemon_nature_id = g.id WHERE "
					+ filter + " = 1 AND a.visible = 1";
			ResultSet resultSet = statement.executeQuery(query);
			logger.info("Query " + query + " executed!");

			while (resultSet.next()) {
				Product product = new Product();
				product.setProductId(resultSet.getInt("a.id"));
				product.setEvAtk(resultSet.getInt("a.ev_atk"));
				product.setEvDef(resultSet.getInt("a.ev_def"));
				product.setEvHp(resultSet.getInt("a.ev_hp"));
				product.setEvInit(resultSet.getInt("a.ev_init"));
				product.setEvSpatk(resultSet.getInt("a.ev_spatk"));
				product.setEvSpdef(resultSet.getInt("a.ev_spdef"));
				product.setLevel(resultSet.getInt("a.level"));
				product.setShiny(resultSet.getBoolean("a.shiny"));
				product.setEvent(resultSet.getBoolean("a.event"));
				product.setNormalPrice(resultSet.getFloat("a.normal_price"));
				product.setSpecialPrice(resultSet.getFloat("a.special_price"));
				product.setId(resultSet.getInt("b.id"));
				product.setName(resultSet.getString("b.name"));
				product.setType1(resultSet.getString("b.type1"));
				product.setLegendary(resultSet.getBoolean("b.legendary"));
				product.setAbility(resultSet.getString("c.ability"));
				product.setAttack1(resultSet.getString("d.attack"));
				product.setAttack2(resultSet.getString("h.attack"));
				product.setAttack3(resultSet.getString("i.attack"));
				product.setAttack4(resultSet.getString("j.attack"));
				product.setGender(resultSet.getString("f.gender"));
				product.setNature(resultSet.getString("g.nature"));

				if (resultSet.getString("b.type2") != null) {
					product.setType2("/ " + resultSet.getString("b.type2"));
				}

				pokemonArray.add(product);
			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return pokemonArray;
	}

	public List<Product> getProductDetail(int productId) {
		ArrayList<Product> pokemonArray = new ArrayList<Product>();
		try {

			if (!isConnected())
				connect();

			Statement statement = connection.createStatement();
			String query = "SELECT a.id, a.ev_atk, a.ev_def, a.ev_hp, a.ev_init, a.ev_spatk, a.ev_spdef, a.level, "
					+ "a.catch_date, a.catch_level, a.catch_place, a.finder, a.event, a.special_price, a.shiny, "
					+ "a.normal_price, b.id, b.name, b.type1, b.type2, b.legendary, c.ability, d.attack, e.sentence, h.attack, i.attack, j.attack, "
					+ "f.gender, g.nature FROM product as a LEFT JOIN pokemon as b on  a.pokemon_id = b.id LEFT JOIN pokemon_ability "
					+ "as c on a.pokemon_ability_id = c.id LEFT JOIN pokemon_attack as d on  a.pokemon_attack_id1 = d.id LEFT JOIN "
					+ "pokemon_attack as h on a.pokemon_attack_id2 = h.id LEFT JOIN pokemon_attack as  i on a.pokemon_attack_id3 = "
					+ "i.id LEFT JOIN pokemon_attack as j on a.pokemon_attack_id4 = j.id LEFT JOIN pokemon_dv as  e on a.pokemon_dv_id = "
					+ "e.id LEFT JOIN pokemon_gender as f on a.pokemon_gender_id = f.id LEFT JOIN pokemon_nature as  g on a.pokemon_nature_id = g.id WHERE a.id = "
					+ productId;
			ResultSet resultSet = statement.executeQuery(query);
			logger.info("Query " + query + " executed!");

			while (resultSet.next()) {
				Product product = new Product();
				product.setProductId(resultSet.getInt("a.id"));
				product.setEvAtk(resultSet.getInt("a.ev_atk"));
				product.setEvDef(resultSet.getInt("a.ev_def"));
				product.setEvHp(resultSet.getInt("a.ev_hp"));
				product.setEvInit(resultSet.getInt("a.ev_init"));
				product.setEvSpatk(resultSet.getInt("a.ev_spatk"));
				product.setEvSpdef(resultSet.getInt("a.ev_spdef"));
				product.setLevel(resultSet.getInt("a.level"));
				product.setCatchDate(resultSet.getString("a.catch_date"));
				product.setCatchLevel(resultSet.getInt("a.catch_level"));
				product.setCatchPlace(resultSet.getString("a.catch_place"));
				product.setFinder(resultSet.getString("a.finder"));
				product.setEvent(resultSet.getBoolean("a.event"));
				product.setShiny(resultSet.getBoolean("a.shiny"));
				product.setSpecialPrice(resultSet.getFloat("a.special_price"));
				product.setNormalPrice(resultSet.getFloat("a.normal_price"));
				product.setId(resultSet.getInt("b.id"));
				product.setName(resultSet.getString("b.name"));
				product.setType1(resultSet.getString("b.type1"));
				product.setLegendary(resultSet.getBoolean("b.legendary"));
				product.setAbility(resultSet.getString("c.ability"));
				product.setAttack1(resultSet.getString("d.attack"));
				product.setSentence(resultSet.getString("e.sentence"));
				product.setAttack2(resultSet.getString("h.attack"));
				product.setAttack3(resultSet.getString("i.attack"));
				product.setAttack4(resultSet.getString("j.attack"));
				product.setGender(resultSet.getString("f.gender"));
				product.setNature(resultSet.getString("g.nature"));

				if (resultSet.getString("b.type2") != null) {
					product.setType2("/ " + resultSet.getString("b.type2"));
				}

				pokemonArray.add(product);
			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return pokemonArray;
	}

	public List<Product> getProductsList() {
		ArrayList<Product> pokemonArray = new ArrayList<Product>();
		try {

			if (!isConnected())
				connect();

			Statement statement = connection.createStatement();
			String query = "SELECT a.level, b.id, b.name, b.type1, b.type2 FROM product as a LEFT JOIN pokemon as b ON a.pokemon_id = b.id";
			ResultSet resultSet = statement.executeQuery(query);
			logger.info("Query " + query + " executed!");

			while (resultSet.next()) {
				Product product = new Product();
				product.setLevel(resultSet.getInt("a.level"));
				product.setId(resultSet.getInt("b.id"));
				product.setName(resultSet.getString("b.name"));
				product.setType1(resultSet.getString("b.type1"));
				product.setType2(resultSet.getString("b.type2"));
				pokemonArray.add(product);
			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return pokemonArray;
	}

	public List<Product> getProductsListByDate() {
		ArrayList<Product> pokemonArray = new ArrayList<Product>();
		try {

			if (!isConnected())
				connect();

			Statement statement = connection.createStatement();
			String query = "SELECT a.level, b.id, b.name, b.type1, b.type2 FROM product as a LEFT JOIN pokemon as b ON a.pokemon_id = b.id ORDER BY TIMESTAMP";
			ResultSet resultSet = statement.executeQuery(query);
			logger.info("Query " + query + " executed!");

			while (resultSet.next()) {
				Product product = new Product();
				product.setLevel(resultSet.getInt("a.level"));
				product.setId(resultSet.getInt("b.id"));
				product.setName(resultSet.getString("b.name"));
				product.setType1(resultSet.getString("b.type1"));
				product.setType2(resultSet.getString("b.type2"));
				pokemonArray.add(product);
			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return pokemonArray;
	}

	public List<Product> getProductsHomeNew() {
		ArrayList<Product> pokemonArray = new ArrayList<Product>();
		try {

			if (!isConnected())
				connect();

			Statement statement = connection.createStatement();
			String query = "SELECT a.id, a.level, a.shiny, b.id, b.name, b.type1, b.type2 FROM product as a LEFT JOIN pokemon as b on  a.pokemon_id = b.id WHERE a.visible = 1 ORDER BY TIMESTAMP DESC LIMIT 6";
			ResultSet resultSet = statement.executeQuery(query);
			logger.info("Query " + query + " executed!");

			while (resultSet.next()) {
				Product product = new Product();
				product.setProductId(resultSet.getInt("a.id"));
				product.setLevel(resultSet.getInt("a.level"));
				product.setShiny(resultSet.getBoolean("a.shiny"));
				product.setId(resultSet.getInt("b.id"));
				product.setName(resultSet.getString("b.name"));
				product.setType1(resultSet.getString("b.type1"));

				if (resultSet.getString("b.type2") != null) {
					product.setType2("/ " + resultSet.getString("b.type2"));
				}

				pokemonArray.add(product);
			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return pokemonArray;
	}

	public List<Product> getProductHome() {
		ArrayList<Product> pokemonArray = new ArrayList<Product>();
		try {

			if (!isConnected())
				connect();

			Statement statement = connection.createStatement();
			String query = "SELECT a.id, a.ev_atk, a.ev_def, a.ev_hp, a.ev_init, a.ev_spatk, a.ev_spdef, a.level, a.deal_of_the_day, "
					+ "a.normal_price, a.special_price, a.shiny, a.visible, b.id, b.name, b.type1, b.type2, c.ability, d.attack, h.attack, i.attack, j.attack, "
					+ "f.gender, g.nature FROM product as a LEFT JOIN pokemon as b on  a.pokemon_id = b.id LEFT JOIN pokemon_ability "
					+ "as c on a.pokemon_ability_id = c.id LEFT JOIN pokemon_attack as d on  a.pokemon_attack_id1 = d.id LEFT JOIN "
					+ "pokemon_attack as h on a.pokemon_attack_id2 = h.id LEFT JOIN pokemon_attack as  i on a.pokemon_attack_id3 = "
					+ "i.id LEFT JOIN pokemon_attack as j on a.pokemon_attack_id4 = j.id LEFT JOIN pokemon_dv as  e on a.pokemon_dv_id = "
					+ "e.id LEFT JOIN pokemon_gender as f on a.pokemon_gender_id = f.id LEFT JOIN pokemon_nature as  g on a.pokemon_nature_id = g.id WHERE a.deal_of_the_day = 1 LIMIT 1";
			ResultSet resultSet = statement.executeQuery(query);
			logger.info("Query " + query + " executed!");

			while (resultSet.next()) {
				Product product = new Product();
				product.setProductId(resultSet.getInt("a.id"));
				product.setEvAtk(resultSet.getInt("a.ev_atk"));
				product.setEvDef(resultSet.getInt("a.ev_def"));
				product.setEvHp(resultSet.getInt("a.ev_hp"));
				product.setEvInit(resultSet.getInt("a.ev_init"));
				product.setEvSpatk(resultSet.getInt("a.ev_spatk"));
				product.setEvSpdef(resultSet.getInt("a.ev_spdef"));
				product.setLevel(resultSet.getInt("a.level"));
				product.setNormalPrice(resultSet.getFloat("a.normal_price"));
				product.setSpecialPrice(resultSet.getFloat("a.special_price"));
				product.setShiny(resultSet.getBoolean("a.shiny"));
				product.setVisible(resultSet.getBoolean("a.visible"));
				product.setId(resultSet.getInt("b.id"));
				product.setName(resultSet.getString("b.name"));
				product.setType1(resultSet.getString("b.type1"));
				product.setAbility(resultSet.getString("c.ability"));
				product.setAttack1(resultSet.getString("d.attack"));
				product.setAttack2(resultSet.getString("h.attack"));
				product.setAttack3(resultSet.getString("i.attack"));
				product.setAttack4(resultSet.getString("j.attack"));
				product.setGender(resultSet.getString("f.gender"));
				product.setNature(resultSet.getString("g.nature"));

				if (resultSet.getString("b.type2") != null) {
					product.setType2("/ " + resultSet.getString("b.type2"));
				}

				pokemonArray.add(product);
			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return pokemonArray;
	}

	//not used in this version: neccessary for admin area
	public List<PokemonProperty> getAllPokemonAbilities() {
		ArrayList<PokemonProperty> pokemonAbillityArray = new ArrayList<PokemonProperty>();
		try {

			if (!isConnected())
				connect();

			Statement statement = connection.createStatement();
			String query = "SELECT ability FROM pokemon_ability";
			ResultSet resultSet = statement.executeQuery(query);
			logger.info("Query " + query + " executed!");

			while (resultSet.next()) {
				PokemonProperty pokemonAbility = new PokemonProperty();
				pokemonAbility.setProperty(resultSet.getString("ability"));
				pokemonAbillityArray.add(pokemonAbility);
			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return pokemonAbillityArray;
	}

	//not used in this version: neccessary for admin area
	public List<PokemonProperty> getAllPokemonAttacks() {
		ArrayList<PokemonProperty> pokemonAttackArray = new ArrayList<PokemonProperty>();
		try {

			if (!isConnected())
				connect();

			Statement statement = connection.createStatement();
			String query = "SELECT attack FROM pokemon_attack";
			ResultSet resultSet = statement.executeQuery(query);
			logger.info("Query " + query + " executed!");

			while (resultSet.next()) {
				PokemonProperty pokemonAttack = new PokemonProperty();
				pokemonAttack.setProperty(resultSet.getString("attack"));
				pokemonAttackArray.add(pokemonAttack);
			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return pokemonAttackArray;
	}

	//not used in this version: neccessary for admin area
	public List<PokemonProperty> getAllPokemonDvs() {
		ArrayList<PokemonProperty> pokemonDvArray = new ArrayList<PokemonProperty>();
		try {

			if (!isConnected())
				connect();

			Statement statement = connection.createStatement();
			String query = "SELECT sentence FROM pokemon_dv";
			ResultSet resultSet = statement.executeQuery(query);
			logger.info("Query " + query + " executed!");

			while (resultSet.next()) {
				PokemonProperty pokemonDv = new PokemonProperty();
				pokemonDv.setProperty(resultSet.getString("sentence"));
				pokemonDvArray.add(pokemonDv);
			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return pokemonDvArray;
	}

	//not used in this version: neccessary for admin area
	public List<PokemonProperty> getAllPokemonGenders() {
		ArrayList<PokemonProperty> pokemonGenderArray = new ArrayList<PokemonProperty>();
		try {

			if (!isConnected())
				connect();

			Statement statement = connection.createStatement();
			String query = "SELECT gender FROM pokemon_gender";
			ResultSet resultSet = statement.executeQuery(query);
			logger.info("Query " + query + " executed!");

			while (resultSet.next()) {
				PokemonProperty pokemonGender = new PokemonProperty();
				pokemonGender.setProperty(resultSet.getString("gender"));
				pokemonGenderArray.add(pokemonGender);
			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return pokemonGenderArray;
	}

	//not used in this version: neccessary for admin area
	public List<PokemonProperty> getAllPokemonNatures() {
		ArrayList<PokemonProperty> pokemonNatureArray = new ArrayList<PokemonProperty>();
		try {

			if (!isConnected())
				connect();

			Statement statement = connection.createStatement();
			String query = "SELECT nature FROM pokemon_nature";
			ResultSet resultSet = statement.executeQuery(query);
			logger.info("Query " + query + " executed!");

			while (resultSet.next()) {
				PokemonProperty pokemonNature = new PokemonProperty();
				pokemonNature.setProperty(resultSet.getString("nature"));
				pokemonNatureArray.add(pokemonNature);
			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return pokemonNatureArray;
	}

	//not used in this version: neccessary for admin area
	public int insertIntoProduct(Product product, Pokemon pokemon,
			PokemonProperty pokemonAbility, PokemonProperty pokemonAttack,
			PokemonProperty pokemonDv, PokemonProperty pokemonGender,
			PokemonProperty pokemonNature) {
		int productId = -1;
		try {

			if (!isConnected())
				connect();

			Statement statement = connection.createStatement();

			// Insert Pokemon with Properties into Product
			String query = "INSERT INTO product (catch_date, catch_level, catch_place, deal_of_the_day, ev_atk, ev_def, ev_hp, ev_init, ev_spatk, ev_spdef, event, finder, level, normal_price, shiny, special_price, timestamp, visible, pokemon_id, pokemon_ability_id, pokemon_attack_id1, pokemon_attack_id2, pokemon_attack_id3, pokemon_attack_id4, pokemon_dv_id, pokemon_gender_id, pokemon_nature_id) VALUES "
					+ " ("
					+ product.getCatchDate()
					+ ","
					+ product.getCatchLevel()
					+ ","
					+ product.getCatchPlace()
					+ ","
					+ product.isDealOfTheDay()
					+ ","
					+ product.getEvAtk()
					+ ","
					+ product.getEvDef()
					+ ","
					+ product.getEvHp()
					+ ","
					+ product.getEvInit()
					+ ","
					+ product.getEvSpatk()
					+ ","
					+ product.getEvSpdef()
					+ ","
					+ product.isEvent()
					+ ","
					+ product.getFinder()
					+ ","
					+ product.getLevel()
					+ ","
					+ product.getNormalPrice()
					+ ","
					+ product.isShiny()
					+ ","
					+ product.getSpecialPrice()
					+ ","
					+ product.getTimestamp()
					+ ","
					+ product.getPokemon().getId()
					+ ","
					+ product.getPokemonAbility().getId()
					+ ","
					+ product.getPokemonAttack1().getId()
					+ ","
					+ product.getPokemonAttack2().getId()
					+ ","
					+ product.getPokemonAttack3().getId()
					+ ","
					+ product.getPokemonAttack4().getId()
					+ ","
					+ product.getPokemonDv().getId()
					+ ","
					+ product.getPokemonGender().getId()
					+ ","
					+ product.getPokemonNature().getId() + "))";

			productId = statement.executeUpdate(query,
					Statement.RETURN_GENERATED_KEYS);
			logger.info("Query " + query + " executed!");

		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return productId;
	}

	public List<Order> getOrdersList(int customerId) {
		ArrayList<Order> orderArray = new ArrayList<Order>();
		try {
			if (!isConnected())
				connect();

			Statement statement = connection.createStatement();
			String query = "SELECT a.basket, a.order_date, a.order_time, a.amount, a.customer_id, b.id, b.name, b.type1, b.type2 FROM order AS a LEFT JOIN pokemon AS b ON a.pokemon_id = b.id WHERE a.customer_id = "
					+ customerId + ")";
			ResultSet resultSet = statement.executeQuery(query);
			logger.info("Query " + query + " executed!");

			while (resultSet.next()) {
				Order order = new Order();

				order.setBasket(resultSet.getBoolean("a.basket"));
				order.setOrderDate(resultSet.getString("a.order_date"));
				order.setOrderTime(resultSet.getString("a.order_time"));
				order.setTotalAmount(resultSet.getFloat("a.amount"));
				order.setCustomerId(resultSet.getInt("a.customer_id"));
				order.setPokemonName(resultSet.getString("b.name"));
				order.setPokemonId(resultSet.getInt("b.id"));
				order.setPokemonType1(resultSet.getString("b.type1"));
				order.setPokemonType2(resultSet.getString("b.type2"));
				orderArray.add(order);
			}

		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return orderArray;
	}

	public void completeOrder(int customerId, int productId, float amount,
			String orderDate, String orderTime) {
		try {
			if (!isConnected())
				connect();

			Statement statement = connection.createStatement();
			String query = "INSERT INTO poketradeshop.order (customer_id, product_id, amount, order_date, order_time) "
					+ "VALUES ('"
					+ customerId
					+ "', '"
					+ productId
					+ "', '"
					+ amount + "', '" + orderDate + "', '" + orderTime + "')";
			statement.executeUpdate(query);
			logger.info("Query " + query + " executed!");

		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		try {
			if (!isConnected())
				connect();

			Statement statement = connection.createStatement();
			String query = "UPDATE `poketradeshop`.`product` SET `visible`='0' WHERE `id`='"
					+ productId + "'";
			statement.executeUpdate(query);
			logger.info("Query " + query + " executed!");

		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	public boolean checkOrderAvailability(int productId) {

		boolean available = false;

		try {

			if (!isConnected())
				connect();

			Statement statement = connection.createStatement();
			String query = "SELECT visible FROM product WHERE id = '"
					+ productId + "'";
			statement.setMaxRows(1);
			ResultSet resultSet = statement.executeQuery(query);
			logger.info("Query " + query + " executed!");

			while (resultSet.next()) {
				available = resultSet.getBoolean("visible");
			}

		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return available;
	}

}
